package dao.impl;

import dao.BookDao;
import db.DbConnection;
import pojo.Book;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl implements BookDao {
    private Connection conn = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    @Override
    public void delete(String bookId) {
        Connection conn = DbConnection.getConnection();
        try {
            String sql = "delete from book where bookId=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, bookId);
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(ps, rs,conn);
        }
    }

    @Override
    public List<Book> searchAll() {
        conn = DbConnection.getConnection();
        List<Book> bookList = new ArrayList<Book>();
        try {
            String sql = "select * from book ";
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                String bookId = rs.getString(1);
                String bookName = rs.getString(2);
                String writer = rs.getString(3);
                String page = rs.getString(4);
                String price = rs.getString(5);
                String press = rs.getString(6);
                String classification = rs.getString(7);
                String total = rs.getString(8);
                String lend = rs.getString(9);
                String surplus = rs.getString(10);
                Book book = new Book(bookId, bookName, writer, page, price, press, classification, total, lend, surplus);
                bookList.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(ps, rs, conn);
        }
        return bookList;
    }

    @Override
    public List<Book> searchClassify(String Classify) {
        conn = DbConnection.getConnection();
        List<Book> bookList = new ArrayList<Book>();
        try {
            String sql = "select * from book where classification=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, Classify);
            rs = ps.executeQuery();
            while (rs.next()) {
                String bookId = rs.getString(1);
                String bookName = rs.getString(2);
                String writer = rs.getString(3);
                String page = rs.getString(4);
                String price = rs.getString(5);
                String press = rs.getString(6);
                String classification = rs.getString(7);
                String total = rs.getString(8);
                String lend = rs.getString(9);
                String surplus = rs.getString(10);
                Book book = new Book(bookId, bookName, writer, page, price, press, classification, total, lend, surplus);
                bookList.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(ps, rs, conn);
        }
        return bookList;
    }

    @Override
    public Book searchId(String id) {
        conn = DbConnection.getConnection();
        Book book = null;
        try {
            String sql = "select * from book where bookId=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                String bookId = rs.getString(1);
                String bookName = rs.getString(2);
                String writer = rs.getString(3);
                String page = rs.getString(4);
                String price = rs.getString(5);
                String press = rs.getString(6);
                String classification = rs.getString(7);
                String total = rs.getString(8);
                String lend = rs.getString(9);
                String surplus = rs.getString(10);
                book = new Book(bookId, bookName, writer, page, price, press, classification, total, lend, surplus);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(ps, rs, conn);
        }
        return book;
    }

    @Override
    public List<Book> searchFuzzy(String fuzzy) {
        conn = DbConnection.getConnection();
        List<Book> bookList = new ArrayList<Book>();
        try {
            String sql = "select * from book where bookId like  \"%\"?\"%\" or bookName like \"%\"?\"%\" or writer like \"%\"?\"%\"";
            ps = conn.prepareStatement(sql);
            ps.setString(1,fuzzy);
            ps.setString(2, fuzzy);
            ps.setString(3, fuzzy);
            rs = ps.executeQuery();
            while (rs.next()) {
                String bookId = rs.getString(1);
                String bookName = rs.getString(2);
                String writer = rs.getString(3);
                String page = rs.getString(4);
                String price = rs.getString(5);
                String press = rs.getString(6);
                String classification = rs.getString(7);
                String total = rs.getString(8);
                String lend = rs.getString(9);
                String surplus = rs.getString(10);
                Book book = new Book(bookId, bookName, writer, page,price, press, classification,total,lend,surplus);
                bookList.add(book);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(ps, rs,conn);
        }
        return bookList;
    }

    @Override
    public void add(Book book) {
        Connection conn = DbConnection.getConnection();
        try {
            String sql = "insert into book values(?,?,?,?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setString(1, book.getBookId());
            ps.setString(2, book.getBookName());
            ps.setString(3, book.getWriter());
            ps.setString(4, book.getPage());
            ps.setString(5, book.getPrice());
            ps.setString(6, book.getPress());
            ps.setString(7, book.getClassification());
            ps.setString(8, book.getTotal());
            ps.setString(9, book.getLend());
            ps.setString(10, book.getSurplus());


            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(ps, rs,conn);
        }
    }

    @Override
    public void update(Book book) {
        Connection conn = DbConnection.getConnection();
        try {
            String sql = "update book set bookName=?,writer=?,page=?,price=?,press=?,classification=?,total=?,lend=?,surplus=? where bookId=?";
            ps = conn.prepareStatement(sql);

            ps.setString(1, book.getBookName());
            ps.setString(2, book.getWriter());
            ps.setString(3, book.getPage());
            ps.setString(4, book.getPrice());
            ps.setString(5, book.getPress());
            ps.setString(6, book.getClassification());
            ps.setString(7, book.getTotal());
            ps.setString(8, book.getLend());
            ps.setString(9, book.getSurplus());
            ps.setString(10, book.getBookId());
            ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DbConnection.close(ps, rs,conn);
        }

    }
}